<?php
require_once('Spreadsheet/Excel/reader.php');
require_once('Verifier.class.php');
require_once("Filter.class.php");
require_once("Accountant.class.php");
//require_once("Debug.inc.php");

class LITTLE_ExcelReader{
	/**
	 * 使用的Spreadsheet_Excel_Reader
	 *
	 * @var Spreadsheet_Excel_Reader
	 */
	protected $reader;


	/**
	 * 当前的电子表格文件名称
	 *
	 * @var string
	 */
	protected $filename;

	/**
	 * 数据库中的表名
	 *
	 * @var string
	 */
	protected $tableName;

	/**
	 * 数据库跟电子表格列的对应关系
	 *
	 * @var array
	 */
	protected $tableColumns;

	/**
	 * 比较的时候，只需比较这个数组中列出的那些值即可
	 *
	 * @var array
	 */
	protected $compareColumns;


	protected $widthIndex = null;
	protected $thicknessIndex = null;
	protected $lengthIndex = null;
	protected $shipIndex = null;
	protected $materialIndex = null;
	protected $mcIndex = null;
	protected $orderNumberIndex = null;
	protected $orderSubitemNumberIndex = null;
	protected $batchNumberIndex = null;
	protected $purchaseNumberIndex = null;
	
	/**
	 * 不能缺少的列的数组，如果电子表格中缺少了，那么就要提示错误
	 *
	 * @var array
	 * @throws InvalidArgumentsException
	 */
	protected $obligatoColumns;


	public function __construct(){
		$this->reader = new Spreadsheet_Excel_Reader();
		$this->reader->setUTFEncoder('iconv');
		$this->reader->setOutputEncoding('utf-8');
	}

	public function __set($name, $value){
		if($name == 'tableName' || $name == 'tableColumns'
		|| $name == 'compareColumns' || $name == 'obligatoColumns'){
			$this->$name = $value;
		}else{
			throw new InvalidArgumentsException('当前类中没有这个属性：'.$name);
		}
	}

	/**
	 * 读取某个电子表格文件，
	 * 同时给$this->filename赋值
	 * @param string $filename 电子表格文件名
	 * @throws FileNotReadableException
	 */
	public function read($filename){
		$this->filename =  basename(iconv('gbk', 'utf-8',$filename));
		$this->reader->read($filename);
	}


	/**
	 * 检查第一行是否正确
	 * @throw LackSomeColumn
	 */
	public function checkFirstRow($type){
		$lackedColumns = "";
		
		//检查文件读取是否正确
		if(!isset($this->reader->sheets[0])){
			throw new ExcelMetaDataError();
		}

		//检查是否缺少某些必须的列
		foreach($this->obligatoColumns as $val){
			if(!in_array($val, $this->reader->sheets[0]['cells'][1]))
				$lackedColumns .= $val.',';
		}

		//如果缺少列，抛出异常
		if(!empty($lackedColumns)){
			throw new LackSomeColumn($lackedColumns);
		}
		
		
		if($type!='main2' && $type!='download'){
			$redundantColumns = "";
			$remarks = array();
			foreach($this->reader->sheets[0]['cells'][1] as $val){
				if(!in_array($val,$this->obligatoColumns) && !in_array($val,$remarks))
					$redundantColumns .= $val.',';
			}
			
			if(!empty($redundantColumns)){
				throw new RedundantColumn($redundantColumns);
			}
			
			
			$sheet = array();
			foreach ($this->reader->sheets[0]['cells'][1] as $key => $val){
				$key--;
				$sheet[$key] = $val;
			}
			
			$result = array_diff_assoc($sheet,$this->obligatoColumns);
			
			if(count($result)!=0){
				foreach ($result as $value){
					if(!in_array($value,$remarks)){
						$result = join(",",$result);
						throw new ExistDifferentColumns($result,$type);
					}
				}
			}
		}
	}


	/**
	 * 判断文件是否已经上传过
	 * 如果已经上传过，返回上回上传的时间
	 *
	 * @return date or boolean
	 */
	function haveUploaded(){
		$query = "select filename, uploadTime from {$this->tableName} where filename = '$this->filename'";
		$result = DB::query($query);
		if(mysqli_num_rows($result) >= 1){
			$row = $result->fetch_assoc();
			return $row['uploadTime'];
		}

		return false;
	}
	
	
	public function getColsIndex(){

		if(!isset($this->reader->sheets[0])){
			throw new ExcelMetaDataError();
		}
		$sheet = $this->reader->sheets[0]['cells'];
		$firstRow = current($sheet);
		
		foreach($firstRow as $key => $val){
			if($val == '船级'){
				$this->shipIndex = $key;
			}else if($val == '材料代码'){
				$this->mcIndex = $key;
			}else if($val == '材质'){
				$this->materialIndex = $key;
			}else if($val == '厚'){
				$this->thicknessIndex = $key;
			}else if($val == '宽'){
				$this->widthIndex = $key;
			}else if($val == '长'){
				$this->lengthIndex = $key;
			}else if($val == '订单号'){
				$this->orderNumberIndex = $key;
			}else if($val == '订单子项号'){
				$this->orderSubitemNumberIndex = $key;
			}else if($val == '批号'){
				$this->batchNumberIndex = $key;
			}else if($val == '购单号'){
				$this->purchaseNumberIndex = $key;
			}
		}
		
		//echo "the indexs is :" . $this->widthIndex."(宽)," . $this->thicknessIndex. "(厚)," . $this->lengthIndex ."(长),". $this->shipIndex ."(船级),". $this->materialIndex . "(材质)," . $this->mcIndex . "(材料代码);" 
		//	. $this->orderNumberIndex ."(订单号);". $this->orderSubitemNumberIndex."(订单子项号);";
			
	}
	
	
	/**
	 * 原因是系统提供的array_walk函数，其接受的函数名称不能是对象的成员函数，
	 * 而这个函数解决了这些问题
	 *
	 * @param array $aa
	 * @param string $fun
	 * @param array $comp
	 * @return bool
	 */
	protected function object_array_walk($aa, $fun, $comp){
		foreach($aa as $key => &$a){
			if($this->$fun($a, $key, $comp)){
				return true;
			}
		}
		return false;
	}
	/**
	 * 注意：虽然函数名称为chineseChars但是，这里应该包括“加片”
	 * 比较两列是否有冲突，这里的冲突这样定义：
	 * 如果两个材料代码的特质都相同，理应算作一个，但是他们的订单号和订单子项号不同，
	 * 而且这个不同时发生在同一个文件中，这样就会造成所谓的冲突
	 * 在使用这个之前，一定要首先调用getColsIndex()函数对那些indexs进行数据填充
	 *
	 * @param array $item
	 * @param string $key
	 * @param array $comp
	 * @return boolean
	 */
	private function chineseCharsConflict(&$item, $key, &$comp){
		/*if($this->widthIndex == null){
			$this->getColsIndex();
		}*/
		//避免因为两者都没有定义，而无法比较的情况
		//比如这两材料代码其实一样，但就是因为他们的“船级”都没有定义，因此无法比较，从而得出结论：没有冲突
		isset($comp[$this->widthIndex]) ? null : $comp[$this->widthIndex] = null;
		isset($comp[$this->lengthIndex]) ? null : $comp[$this->lengthIndex] = null;
		isset($comp[$this->thicknessIndex]) ? null : $comp[$this->thicknessIndex] = null;
		isset($comp[$this->shipIndex]) ? null : $comp[$this->shipIndex] = null;
		isset($comp[$this->materialIndex]) ? null : $comp[$this->materialIndex] = null;
		isset($comp[$this->mcIndex]) ? null : $comp[$this->mcIndex] = null;
		isset($comp[$this->orderNumberIndex]) ? null : $comp[$this->orderNumberIndex] = null;
		isset($comp[$this->orderSubitemNumberIndex]) ? null : $comp[$this->orderSubitemNumberIndex] = null;
		isset($comp[$this->batchNumberIndex]) ? null : $comp[$this->batchNumberIndex] = null;
		isset($comp[$this->purchaseNumberIndex]) ? null : $comp[$this->purchaseNumberIndex] = null;
		
		
		isset($item[$this->widthIndex]) ? null : $item[$this->widthIndex] = null;
		isset($item[$this->lengthIndex]) ? null : $item[$this->lengthIndex] = null;
		isset($item[$this->thicknessIndex]) ? null : $item[$this->thicknessIndex] = null;
		isset($item[$this->shipIndex]) ? null : $item[$this->shipIndex] = null;
		isset($item[$this->materialIndex]) ? null : $item[$this->materialIndex] = null;
		isset($item[$this->mcIndex]) ? null : $item[$this->mcIndex] = null;
		isset($item[$this->orderNumberIndex]) ? null : $item[$this->orderNumberIndex] = null;
		isset($item[$this->orderSubitemNumberIndex]) ? null : $item[$this->orderSubitemNumberIndex] = null;
		isset($comp[$this->batchNumberIndex]) ? null : $comp[$this->batchNumberIndex] = null;
		isset($comp[$this->purchaseNumberIndex]) ? null : $comp[$this->purchaseNumberIndex] = null;
		/**
		 * 这里的顺序不是随意的，什么随机性大，就应该放在前面，
		 * 这样能减少比较的次数，从而提高效率
		 */
		if($comp[$this->widthIndex] == $item[$this->widthIndex] &&
			$comp[$this->lengthIndex] == $item[$this->lengthIndex] &&
			$comp[$this->thicknessIndex] == $item[$this->thicknessIndex] &&
			$comp[$this->shipIndex] == $item[$this->shipIndex] &&
			$comp[$this->materialIndex] == $item[$this->materialIndex] &&
			$comp[$this->mcIndex] == $item[$this->mcIndex] &&
			$comp[$this->batchIndex] == $item[$this->batchIndex] &&
			$comp[$this->purchaseIndex] == $item[$this->purchaseIndex]){
				//如果特质一样，但是订单号某个不一样，那么有冲突
				if($comp[$this->orderNumberIndex] != $item[$this->orderNumberIndex] ||
				$comp[$this->orderSubitemNumberIndex] != $item[$this->orderSubitemNumberIndex]){
					//echo ('conflict<br />');
					return true;	
				}else{
					//echo ('no conflict<br />');
					return false;
				}
			}else{
				//echo ('no conflict<br />');
				return false;
			}
	}
	
	
	/**
	 * 检测这个产品质量证明书号是否已经存在于数据库中？
	 * 这个函数可以用来避免这样一个问题： 总表三文件中的产品质量证明书号在以前上传的总表中不存在，这肯定是一个问题，
	 *
	 * @param string $cn
	 * @return boolean or array
	 */
	public function isCertificationNumberUploaded($cn){
		$wheres = array();
		if(is_array($cn)){
			foreach($cn as $val){
				array_push($wheres, "certificationNumber = '$val'");
			}
			$wheres = join(" or ", $wheres);
		}else{
			$wheres = "certificationNumber = '$cn'";
		}
		$query = "select certificationNumber from ".$this->tableName." where $wheres";
		DB::query($query);
		if(mysqli_num_rows(DB::getResult()) > 0){
			$ret = array();
			$result = DB::getResult();
			while($row = $result->fetch_assoc()){
				array_push($ret, $row['certificationNumber']);
			}
			return $ret;
		}else{
			return false;
		}
	}


	/**
	 * 从数组$cnArray中获得一个还没有上传的产品质量证明书号
	 * 使用递归方式，因为只需要找到一个，所以可以使用二分查找法。
	 *
	 * @param array $cnArray
	 * @return string
	 */
	protected function getOneHaveNotUploadedCertificationNumber($cnArray){
		$inString = "'".join("', '", $cnArray)."'";
		$query = "select count(distinct certificationNumber) from little_main where certificationNumber in($inString)";
		//echo "$query".'<br />';
		$result = DB::query($query);
		$row = $result->fetch_assoc();
		//echo "find row: ".$row['count(distinct certificationNumber)'].'<br />';
		//echo "array row: ".count($cnArray).'<br />';
		if($row['count(distinct certificationNumber)'] == count($cnArray)){
			return false;
		}else{
			if(count($cnArray) == 1){
				return $cnArray[0];
			}else{
				$cnArrayArray = array_chunk($cnArray, (count($cnArray)+1)/2);
				$firstHalf = $this->getOneHaveNotUploadedCertificationNumber($cnArrayArray[0]);
				if($firstHalf == false){
					$secondHalf = $this->getOneHaveNotUploadedCertificationNumber($cnArrayArray[1]);
					if($secondHalf == false){
						throw new CustomException("这里肯定有个错误， 因为这些cn中肯定有未上传的产品质量证明书号，但是分开计算后没有找到");
					}else{
						return $secondHalf;
					}
				}else{
					return $firstHalf;
				}
			}
		}
	}

	/**
	 * 用于检查这个上传的文件中是否有总表中不存在的产品质量证明书号
	 * 如果有的话，会报告错误，而且停止上传。
	 * 对于这个函数，如果没有的话,返回null, 否则返回
	 * 包含所有产品质量证明书号的数组
	 *
	 * 后来添加$count这个参数，为的是提高效率，因为这个函数实在无法在优化，
	 * 所以只能减少他返回的个数。因此添加$count变量，一般变量为10比较好。
	 * 但现在因为时间问题，只适用二分查找法找到一个,但仍然以数组方式返回，这样能得到很好的扩展性。
	 *
	 */
	public function getHaveNotUploadedCertificationNumber($count = null){

/*		echo "<pre>";
		var_dump($this->reader->sheets[0]['cells']);
		echo "</pre>";*/
		$cnArray = $this->getCertificationNumberArray();

		$cnArray = array_unique($cnArray);
/*		echo "<pre>";
		var_dump($cnArray);
		echo "</pre>";*/
		$oneHaveNot = $this->getOneHaveNotUploadedCertificationNumber($cnArray);


		if($oneHaveNot !== false){
			$haveNot = array($oneHaveNot);
			return $haveNot;
		}else{
			return null;
		}
	}
	
	
	/**
	 * 获取上传表格中在数据库中已经存在的产品质量证明书号
	 * 已排除表格中的重复数据
	 * @return unknown_type
	 */
	public function getHaveUploadedCertificationNumber(){

		$have = array();
		$wheres = array();
		$cnArray = $this->getCertificationNumberArray();
		$cnArray = array_unique($cnArray);
		if(count($cnArray) == 0){
			return null;
		}
		foreach($cnArray as $val){
			array_push($wheres, "certificationNumber = '$val'");
		}
		$wheres = join(" or ", $wheres);
		$query = "select distinct certificationNumber from little_main where $wheres";

		DB::query($query);
		if(mysqli_num_rows(DB::getResult()) > 0){
			$result = DB::getResult();
			while($row = $result->fetch_assoc()){
				array_push($have, $row['certificationNumber']);
			}
		}
		if(count($have) > 0){
			return $have;
		}else{
			return null;
		}
	}
	
	/**
	 * 获得表格中的所有产品质量证明书号
	 * 注意：没有排除重复
	 * 作为数组返回
	 *
	 * @return array
	 */
	function getCertificationNumberArray(){
		$sheet = $this->reader->sheets[0]['cells'];
		//首先将所有certificationNumber组成一个array
		$cnArray = array();
		$firstRow = current($sheet);

		///dprint($sheet);
		//dprint($firstRow);

		//查找"材料代码"在哪一列,将其值放到$cnKey中
		$cnKey = null;
		foreach($firstRow as $key => $val){
			if($val == '产品质量证明书号'){
				$cnKey = $key;
				break;
			}
		}
		//如果没有材料代码这一项，抛出异常
//		if($cnKey == null){
//			throw new LackSomeColumn("产品质量证明书号");
//		}

		//将“材料代码”放到数组中
		while($row = next($sheet)){
			if(!isset($row[$cnKey])){
				continue;
			}
			$col = $row[$cnKey] ;
			$col = trim($col);
			if(!empty($col)){
				array_push($cnArray, $col);
			}
		}

		return $cnArray;
	}
	
	/**
	 * 获得表格中的所有产品质量证明书号和产品质量证明书编号
	 * 注意：没有排除重复
	 * 作为数组返回
	 *
	 * @return array
	 */
	function getCertificationNumberAndCertificationCodificationArray(){
		$sheet = $this->reader->sheets[0]['cells'];
		//首先将所有certificationNumber组成一个array
		$cnArray = array();
		$firstRow = current($sheet);

		///dprint($sheet);
		//dprint($firstRow);

		//查找"材料代码"在哪一列,将其值放到$cnKey中
		$cnKey = null;
		$ccKey = null;
		foreach($firstRow as $key => $val){
			if($val == '产品质量证明书号'){
				$cnKey = $key;
			}else if($val == '产品质量证明书编号'){
				$ccKey = $key;
				break;
			}
		}
		//如果没有材料代码这一项，抛出异常
		if($cnKey == null){
			throw new LackSomeColumn("产品质量证明书号");
		}else if($ccKey == null){
			throw new LackSomeColumn("产品质量证明书编号");
		}

		//将“材料代码”放到数组中
		while($row = next($sheet)){
			if(!isset($row[$cnKey])){
				continue;
			}
			$col = $row[$cnKey] ;
			$col = trim($col);
			if(!empty($col)){
				array_push($cnArray, array($col, isset($row[$ccKey])?$row[$ccKey]:null));
			}
		}

		return $cnArray;
	}
	
	/**
	 * 获得表格中的所有质保书号，订单号，件数，批号
	 * 注意：没有排除重复
	 * 作为数组返回
	 *
	 * @return array
	 */
	function getMainArray(){
		$sheet = $this->reader->sheets[0]['cells'];
		$cnArray = array();
		$firstRow = current($sheet);

		///dprint($sheet);
		//dprint($firstRow);

		$cnKey = null;
		$orderKey = null;
		$countKey = null;
		$batchKey = null;
		$orderSubitemKey = null;
		foreach($firstRow as $key => $val){
			if($val == '产品质量证明书号'){
				$cnKey = $key;
			}else if($val == '订单号'){
				$orderKey = $key;
			}else if($val == '数量'){
				$countKey = $key;
			}else if($val == '批号'){
				$batchKey = $key;
			}else if($val == '订单子项号'){
				$orderSubitemKey = $key;
			}
		}
		//如果没有材料代码这一项，抛出异常
		if($cnKey == null){
			throw new LackSomeColumn("产品质量证明书号");
		}else if($orderKey == null){
			throw new LackSomeColumn("订单号");
		}else if($countKey == null){
			throw new LackSomeColumn("数量");
		}else if($batchKey == null){
			throw new LackSomeColumn("批号");
		}else if($orderSubitemKey == null){
			throw new LackSomeColumn("订单子项号");
		}

		//将“材料代码”放到数组中
		while($row = next($sheet)){
			if(!isset($row[$cnKey])){
				continue;
			}
			$col = $row[$cnKey] ;
			$col = trim($col);
			if(!empty($col)){
				array_push($cnArray, array($col, isset($row[$orderKey])?$row[$orderKey]:null,isset($row[$countKey])?$row[$countKey]:null,isset($row[$batchKey])?$row[$batchKey]:null,isset($row[$orderSubitemKey])?$row[$orderSubitemKey]:null));
			}
		}

		return $cnArray;
	}
	
	/**
	 * 获得表格中的所有发货时间，订单号，订单子项号，车号，同时判断这几列加上序号列是否缺少
	 * 注意：没有排除重复
	 * 作为数组返回
	 *
	 * @return array
	 */
	function getDownloadArray(){
		$sheet = $this->reader->sheets[0]['cells'];
		$cnArray = array();
		$firstRow = current($sheet);

		///dprint($sheet);
		//dprint($firstRow);

		$dateKey = null;
		$vehicleKey = null;
		$orderKey = null;
		$orderSubitemKey = null;
		$countKey = null;
		foreach($firstRow as $key => $val){
			if($val == '序号'){
				$serialKey = $key;
			}else if($val == '发货时间'){
				$dateKey = $key;
			}else if($val == '订单号'){
				$orderKey = $key;
			}else if($val == '车号'){
				$vehicleKey = $key;
			}else if($val == '订单子项号'){
				$orderSubitemKey = $key;
			}
		}
		//如果没有材料代码这一项，抛出异常
		if($dateKey == null){
			throw new LackSomeColumn("发货时间");
		}else if($orderKey == null){
			throw new LackSomeColumn("订单号");
		}else if($vehicleKey == null){
			throw new LackSomeColumn("车号");
		}else if($orderSubitemKey == null){
			throw new LackSomeColumn("订单子项号");
		}else if($serialKey == null){
			throw new LackSomeColumn("序号");
		}
		while($row = next($sheet)){
			if(!isset($row[$serialKey])){
				continue;
			}
			if($row[$dateKey]!=null){
				$this->correctDateFormate($row[$dateKey]);
				if(Verifier::getInstance()->isDATE($row[$dateKey])){
				 	$date = $sheetRow[$i+1] = date('Y/m/d', strtotime($row[$dateKey]));
				 }
				$date = trim($date);
			}else{
				$date = null;
			}
			$col = $row[$serialKey];
			if(!empty($col)){
				array_push($cnArray, array($date, isset($row[$vehicleKey])?$row[$vehicleKey]:null,isset($row[$orderKey])?$row[$orderKey]:null,isset($row[$orderSubitemKey])?$row[$orderSubitemKey]:null,$col));
			}
		}

		return $cnArray;
	}
	
	/**
	 * 获得表格中的所有订单号，订单子项号，批号，发车证书号
	 * 注意：没有排除重复
	 * 作为数组返回
	 *
	 * @return array
	 */
	function getMainDownloadArray(){
		$sheet = $this->reader->sheets[0]['cells'];
		$cnArray = array();
		$firstRow = current($sheet);
		
		$orderKey = null;
		$orderSubitemKey = null;
		$batchKey = null;
		$facheKey = null;
		foreach($firstRow as $key => $val){
			if($val == '批号'){
				$batchKey = $key;
			}else if($val == '订单号'){
				$orderKey = $key;
			}else if($val == '发车证书号'){
				$facheKey = $key;
			}else if($val == '订单子项号'){
				$orderSubitemKey = $key;
			}
		}
		//如果没有材料代码这一项，抛出异常
		if($batchKey == null){
			throw new LackSomeColumn("批号");
		}else if($orderKey == null){
			throw new LackSomeColumn("订单号");
		}else if($facheKey == null){
			throw new LackSomeColumn("发车证书号");
		}else if($orderSubitemKey == null){
			throw new LackSomeColumn("订单子项号");
		}
		while($row = next($sheet)){
			if(!isset($row[$orderKey])){
				continue;
			}
			$col = $row[$orderKey];
			$col = trim($col);
			if(!empty($col)){
				array_push($cnArray, array($col, isset($row[$orderSubitemKey])?$row[$orderSubitemKey]:null,isset($row[$batchKey])?$row[$batchKey]:null,isset($row[$facheKey])?$row[$facheKey]:null));
			}
		}

		return $cnArray;
	}
	
	/**
	 * 表格中是否存在相同的产品质量证明书号
	 * @return unknown_type
	 */
	function haveSameCertificationNumber(){
		$cnArray = $this->getCertificationNumberArray();
		$sameCns = array();
		$cnsIndex = array();
		foreach($cnArray as $key => $val){
			if(!isset($cnsIndex[$val])){
				$cnsIndex[$val] = false;
			}else{
				$cnsIndex[$val] = true;
			}
		}

		foreach($cnsIndex as $key => $val){
			$val == true?array_push($sameCns, $key):null;
		}

		if(empty($sameCns)){
			return false;
		}else{
			return $sameCns;
		}
	}
	/**
	 * 将电子表格中的信息读取数据库，读入之前必须先设置
	 * $tableName 和 $tableColumns
	 *
	 * @throws DBConnectException DBQueryException CustomException
	 */
	public function readIntoDB(){


		date_default_timezone_set('Asia/Shanghai');
		$currentTime = date('Y/m/d H:i:s');

		$valuesStr = "";
		//dprint($this->reader->sheets[0]['cells']);
		for($i = 2; $i <= $this->reader->sheets[0]['numRows']; $i++){
			if(!isset($this->reader->sheets[0]['cells'][$i])){
				continue;
			}
			try{
			$formatedSheetRow = $this->getFormatedFromSheetRow($this->reader->sheets[0]['cells'][$i]);
			}catch(CustomException $e){
				//var_dump($formatedSheetRow);
				throw new CustomException($e.'Excel表格出错行数：'.$i);
			}
			if($formatedSheetRow == null){
				continue;
			}
			
			//下面对数据进行过滤
			foreach($formatedSheetRow as $key => $val){
				$formatedSheetRow[$key] = Filter::forDBInsertion($val);
			}

			$value = "";
			$value = ", ('".join("', '", $formatedSheetRow)."', '$currentTime', '{$this->filename}')\n";

			$valuesStr .= $value;
		}

		$valuesStr = substr($valuesStr, 1);
		$cols = "";
	    $cols = " (`".join("`, `", array_unique(array_values($this->tableColumns)))."`, `uploadTime`, `filename`) ";
		$query = "insert into {$this->tableName}$cols values $valuesStr;";
		DB::query($query);
	}


	/**
	 * 将从excel表格中获得的数据格式化,丢弃了“重量”这一列，
	 * 因为可以用数量和单重相乘计算出来，如果某一行的材料代码列没有数据，那么返回null
	 *
	 * @param array $sheetRow
	 * @return array
	 * @throws CustomException
	 */
	protected function getFormatedFromSheetRow($sheetRow){

		$ret = array();
		if(is_array($this->tableColumns))
			foreach($this->tableColumns as $key => $val){
				$ret[$val] = null;
			}
		$count = count($sheetRow);
		for($i = 0; $i < $count; $i++){
			if(isset($this->tableColumns[$this->reader->sheets[0]['cells'][1][$i+1]])){
				$key = $this->tableColumns[$this->reader->sheets[0]['cells'][1][$i+1]];
				if(!isset($sheetRow[$i+1]) || empty($sheetRow[$i+1])){
					$count++;
				}else{
					//如果这一行材料代码为空，那么返回null
					if($key == 'facheNumber' || $key == 'certificationNumber'){
						$sheetRow[$i+1] = trim($sheetRow[$i+1]);
						if(empty($sheetRow[$i+1])){
							return null;
						}else if(iconv_strlen($sheetRow[$i + 1], 'utf-8') > 32){
							throw new DataTooLongForDBInsertion($sheetRow[$i+1], 32);
						}
					}else if($key == 'rukuDate' || $key == 'chukuDate' || $key == 'shipmentDate'){
						//需要修正一下时间格式的不统一
						$this->correctDateFormate($sheetRow[$i+1]);
						 if(Verifier::getInstance()->isDATE($sheetRow[$i+1])){
						 	$sheetRow[$i+1] = date('Y/m/d', strtotime($sheetRow[$i+1]));

						 }else{
						 	throw new InvalidDateFormat($sheetRow[$i+1]);
						 }
					}
					$ret[$key] = $sheetRow[$i+1];
				}
			}
		}

	/*	echo('<pre>');
		var_dump($sheetRow);
		echo '<br /> count is :'.$count;
		die();*/
		return $ret;
	}


	/**
	 * 修正时间格式的不同统一性
	 * 因为Excel表中为d/m/Y
	 * 而strtotime时以m/d/Y这样解析
	 *
	 * @param string $dateVal
	 * @return string
	 * @throws  InvalidDateFormat
	 */
	protected function correctDateFormate(&$dateVal){

		$dateArray = explode('/', $dateVal);
		if(count($dateArray) !== 3){
			throw new InvalidDateFormat($dateVal);
		}
		$temp = $dateArray[0];
		$dateArray[0] = $dateArray[1];
		$dateArray[1] = $temp;
		$dateVal = join('/', $dateArray);

		return $dateVal;
	}
	
	public function getReader(){
		return $this->reader;
	}
}

?>